/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package fase2.pt.iul.sid.database;

import fase2.pt.iul.sid.model.ComentarioAoPratoModel;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

/**
 *
 * @author Ricardo Marques
 */
public class ComentarioAoPratoDAO {
   
    private FactoryConnection fc = null;
    
    public ComentarioAoPratoDAO(FactoryConnection fc){
        this.fc = fc;
    }
    
    public List<ComentarioAoPratoModel> getComentariosPrato(String email_autor, int id_prato, int id_estabelecimento,String texto_comentario, int avaliacao, boolean foto){
        
    List<ComentarioAoPratoModel> ret = new LinkedList<>();;

        int resultsetType =0;
        ConnectionHandler ch = new ConnectionHandler();
                
        try {
            
            fc.returnResultSetSelectStatement("select u.nome,u.email,cp.comentario,cp.nota,cp.idPrato,e.designacao,f.idFotografia,p.descricao,e.idEstabelecimento,p.idPrato from ComentarioAoPrato cp " +
                                            "inner join menuDoEstabelecimento m on m.idPrato=cp.idPrato " +
                                            "outer apply (select top 1* from Fotografia where idPrato=cp.idPrato order by cp.idPrato) as f " +
                                            "inner join Estabelecimento e on e.idEstabelecimento=m.idEstabelecimento " +
                                            "inner join Prato p on p.idPrato=m.idPrato " +
                                            "inner join Utilizador u on cp.email=u.email " +
                                            "where cp.email like '%"+email_autor+"%' " +
                                            "and (m.idEstabelecimento="+id_estabelecimento+" or 0="+id_estabelecimento+") " +
                                            "and (m.idPrato="+id_prato+" or 0="+id_prato+") " +
                                            "and cp.comentario like '%"+texto_comentario+"%' " +
                                            "and cp.nota >="+avaliacao, resultsetType, ch);                    
            
            if(ch.r!=null){
                                
                while(ch.r.next()){
                        
                    boolean fotoPrato = ch.r.getInt("idFotografia") != 0;
                    
                    if(!fotoPrato && foto){
                        
                    }else{
                                                                  
                        ComentarioAoPratoModel cp = new ComentarioAoPratoModel(ch.r.getString("nome"),ch.r.getString("email"), ch.r.getString("comentario"),ch.r.getInt("nota"),ch.r.getString("designacao"),ch.r.getString("descricao"),fotoPrato,ch.r.getInt("idEstabelecimento"),ch.r.getInt("idPrato"));
                        ret.add(cp);
                    }
                    
                }
            }
            
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
        return ret;
    }
    
    public ComentarioAoPratoModel getComentarioPrato(int id_prato, String email){
        
        List<ComentarioAoPratoModel> lista = getComentariosPrato(email, id_prato, 0, "", 0, false);

        if(lista.size()>0){
            return lista.get(0);
        }else{
            return null;
        }
        
    }

    public boolean alterarComentarioAoPrato(int id, String email_autor, String comentario, int nota) {
        
        ConnectionHandler ch = new ConnectionHandler();
        
        fc.updateStatement("update ComentarioAoPrato set comentario='"+comentario+"',nota="+nota+" where email='"+email_autor+"' and idPrato="+id, ch);
        
        return "1".equals(ch.returnStatus);
        
    }

    public boolean apagarComentarioAoPrato(int id, String email_autor) {
        
        ConnectionHandler ch = new ConnectionHandler();
        
        fc.updateStatement("delete ComentarioAoPrato WHERE email='"+email_autor+"' and idPrato="+id, ch);
        
        return "1".equals(ch.returnStatus);
        
    }
    
    public List<ComentarioAoPratoModel> getListaComentarios(int id, int numero_comentarios_mostrar, int numero_pagina){
        
        List<ComentarioAoPratoModel> ret = new LinkedList<>();
        
        int resultsetType =0;
        ConnectionHandler ch = new ConnectionHandler();
        
        int inicio = 0;
        int fim = 0;
        
        if(numero_pagina<=1){
            inicio = 0;
            fim = numero_comentarios_mostrar;
        }else{
            inicio = numero_comentarios_mostrar * (numero_pagina-1);
            fim = numero_comentarios_mostrar * numero_pagina;
        }
                
        try {
                fc.returnResultSetSelectStatement("SELECT * FROM ( SELECT    ROW_NUMBER() OVER ( ORDER BY c.idPrato ) AS RowNum, c.idPrato,c.email,c.comentario,c.nota,u.nome,p.descricao,e.designacao,e.idEstabelecimento " +
                                                    "FROM ComentarioAoPrato c inner join Utilizador u on c.email=u.email inner join Prato p on p.idPrato=c.idPrato " +
                                                    "inner join menuDoEstabelecimento m on m.idPrato=c.idPrato " +
                                                    "inner join Estabelecimento e on e.idEstabelecimento=m.idEstabelecimento " +
                                                    "where (c.idPrato="+id+" or 0="+id+")   )AS RowConstrainedResult " +
                                                    "WHERE   RowNum > "+inicio+" " +
                                                    "AND RowNum <= "+fim+" " +
                                                    "ORDER BY RowNum", resultsetType, ch);

            if(ch.r!=null){
                                
                while(ch.r.next()){
                    
                    ComentarioAoPratoModel cp = new ComentarioAoPratoModel(ch.r.getString("nome"),ch.r.getString("email"), ch.r.getString("comentario"),ch.r.getInt("nota"),ch.r.getString("designacao"),ch.r.getString("descricao"),false,ch.r.getInt("idEstabelecimento"),ch.r.getInt("idPrato"));
                    ret.add(cp);

                }
            }
            
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
        return ret;
        
    }
    
    public boolean inserirComentarioAoPrato(int id, String email, String comentario,int nota){
        
        ConnectionHandler ch = new ConnectionHandler();
        
        fc.updateStatement("insert into ComentarioAoPrato values('"+email+"',"+id+",'"+comentario+"',"+nota+")", ch);
        
        return "1".equals(ch.returnStatus);
        
    }
    
}
